USE [DSRSystem_Temp]
GO

/****** Object:  StoredProcedure [dbo].[spCDPrepareSnapshotData]    Script Date: 1/26/2016 12:54:42 PM ******/
DROP PROCEDURE [dbo].[spCDPrepareSnapshotData]
GO

/****** Object:  StoredProcedure [dbo].[spCDPrepareSnapshotData]    Script Date: 1/26/2016 12:54:42 PM ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO




CREATE PROCEDURE [dbo].[spCDPrepareSnapshotData]
@ChangedSince datetime
AS
BEGIN
	SET NOCOUNT ON;


	

	IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'dbo.Analyst') AND type in (N'U'))	 
	BEGIN
		CREATE TABLE [dbo].Analyst(
		[Id] uniqueidentifier NOT NULL,
	    [FirstName] nvarchar(256),
		[LastName] nvarchar(256),
		[Picture] varbinary(max),
		[Email] nvarchar(256),
		) ON [PRIMARY]
	END



	


	TRUNCATE TABLE dbo.Analyst;
	INSERT INTO dbo.Analyst 
	   select DISTINCT 
		   s.IL1UserId as AnalystId
	       ,up.FirstName
		   ,up.LastName
		   ,up.Photo as Picture 
		   ,m.Email 
	from dsrsystem.dbo.Sectors s 
	left outer join dsrsystem.dbo.UserProfiles up on s.IL1UserId = up.UserId
	left outer join dsrsystem.dbo.aspnet_Membership m on s.IL1UserId = m.UserId
	Where s.Type =1



	IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'dbo.PeerGroups') AND type in (N'U'))	 
	BEGIN
		CREATE TABLE [dbo].[PeerGroups](
		[Id] uniqueidentifier NOT NULL,
	    [Name] nvarchar(256) NOT NULL,
		[SectorType] int, -- 	PeerGroup = 1,        GICS = 2,        RootPeerGroup = 3		
		) ON [PRIMARY]
	END

	TRUNCATE TABLE dbo.PeerGroups;
	INSERT INTO dbo.PeerGroups 
	SELECT s.SectorId as Id, s.Name, s.Type AS SectorType
		FROM DSRSystem.dbo.Sectors s 		
		WHERE s.Type <> 2
		ORDER BY s.Name




	IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'dbo.GICSStructure') AND type in (N'U'))	 
	BEGIN
		CREATE TABLE [dbo].[GICSStructure](
		[Id] uniqueidentifier NOT NULL,
	    [Name] nvarchar(256) NOT NULL,
		[Number] nvarchar(256) NOT NULL, 
		[GICSLevel] int NOT NULL, -- None = 0, Sector = 2, IndustryGroup = 4, Industry = 6, SubIndustry = 8	
		[IndustryId] uniqueidentifier 
		) ON [PRIMARY]
	END

	TRUNCATE TABLE dbo.GICSStructure;
	INSERT INTO dbo.GICSStructure 
	SELECT s.SectorId as Id, s.Name, s.Number, Len(s.Number) as GICSLevel, s.ParentSector as IndustryId
		FROM DSRSystem.dbo.Sectors s 		
		WHERE s.Type = 2
		ORDER BY s.Name






	IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'dbo.CorporateDataFromDSR') AND type in (N'U'))	 
	BEGIN
		CREATE TABLE [dbo].[CorporateDataFromDSR](
		[Id] uniqueidentifier NOT NULL,
		[CompanyName] [nvarchar](200) NOT NULL,
		[CompanyNameLowercase] [nvarchar](200) NOT NULL,
	    [CompanyType] nvarchar(64) NOT NULL,
		[TemplateType] nvarchar(32) NOT NULL, 
		[RootPeerGroup] nvarchar(128) NOT NULL, 
		PeerGroupShortName nvarchar(128) NOT NULL, 
		GICSSubIndustry nvarchar(128) NOT NULL, 
        PeerGroupAndSubIndustry nvarchar(128) NOT NULL, 
        GICSIndustry nvarchar(128) NOT NULL, 
        GICSIndustryGroup nvarchar(128) NOT NULL, 
        GICSSector nvarchar(128) NOT NULL, 
		[HasEsgResearch] bit, 
		[HasControversyResearch] bit,	
		[HasGccsResearch] bit, 
		[PIResearchState] int, 
		[HasGovernanceResearch] bit,
		[PhoneNumber1] nvarchar(128), 
		[PhoneNumber2] nvarchar(128),
		[AnalystId] uniqueidentifier,
		[AnalystCommentary] nvarchar(1500),
		[AnnualReport] nvarchar(max),
		[CSRReport] nvarchar(max),
		[LatestFeedbackRequest]  datetime,
		[CompanyResponse]  datetime,
		[SPLCode] nvarchar(128),
		[LastUpdate] datetime,


		) ON [PRIMARY]
	END



	--  check is there new stuff modified on corporate data
	--  if data is not new skip 

	declare @TempLastUpdate datetime;

	select  top 1 @TempLastUpdate = [LastUpdate] from dbo.CorporateDataFromDSR order by lastupdate desc;
	--select @TempLastUpdate;

	IF (@ChangedSince > @TempLastUpdate)  -- no new changes 
		Return;
		
	 	
		
    TRUNCATE TABLE dbo.CorporateDataFromDSR;
	

	; with 
	GICSStructure as  
	(
		select si.SectorId
			   , si.Name as GICSSubIndustry
			   , si.ShortName as PeerGroupAndSubIndustry
			   , i.Name as GICSIndustry
			   , ig.Name as GICSIndustryGroup 
			   , s.Name as GICSSector 
		from   dsrsystem.[dbo].[Sectors]  si
		left outer join  dsrsystem.[dbo].[Sectors]  i on si.ParentSector = i.SectorId
		left outer join  dsrsystem.[dbo].[Sectors]  ig on i.ParentSector = ig.SectorId
		left outer join  dsrsystem.[dbo].[Sectors]  s on ig.ParentSector = s.SectorId
		where si.Type = 2
	)
	,
	RootPeergroups as 
	(  
	    select p.SectorId, p.IL1UserId as AnalystId, IIF (p.Name like '%#', 'Type B','Type A' ) as TemplateType, p.ShortName as PeerGroupShortName, r.Name as RootPeerGroup
		from   dsrsystem.[dbo].[Sectors]  p
		left outer join  dsrsystem.[dbo].[Sectors]  r on p.RootSectorId = r.SectorId		
		where p.Type = 1
	)
	,
	CompaniesWithIndicators  as
	(
		select distinct c.CompanyId
		from  dsrsystem.[dbo].Companies c 
		inner join dsrsystem.[dbo].IndicatorsInCompanies ic on c.CompanyId = ic.CompanyId
		inner join dsrsystem.[dbo].Indicators i  on ic.IndicatorId = i.IndicatorId
		where  i.IndicatorTypeId = 1 AND c.IsParked = 0 AND c.IsControversyResearchOnly = 0 AND c.WorkingVersion > 1002
			
	)
	,
	CorporateDatas  as 
	(
		select	c.CompanyId as Id
				, c.Name as CompanyName
				, Lower(c.Name) as CompanyNameLowercase
				, IIF(ct.Name = 'Private', 'Private' , 'Public' ) as CompanyTpe
				, p.TemplateType
				, p.RootPeerGroup
				, p.PeerGroupShortName				
				, g.GICSSubIndustry
				, g.PeerGroupAndSubIndustry
				, g.GICSIndustry
				, g.GICSIndustryGroup
				, g.GICSSector
				, IIF(i.companyId is null, 0, 1) as HasEsgResearch
				, 0 as HasControversyResearch
				, 0 as HasGccsResearch
				, 0 as PIResearchState
				, 0 as HasGovernanceResearch
				, c.Phone1 as PhoneNumber1 
				, c.Phone2 as PhoneNumber2 				
				, p.AnalystId
				, c.FinalAnalystCommentary as AnalystCommentary 
				, c.FinalLatestAnnualReport as AnnualReport
                , c.FinalLatestSustainabilityReport as CSRReport 
                , c.FinalLatestRequestedFeedback as LatestFeedbackRequest 
                , c.FinalLatestReceivedFeedback as CompanyResponse
                , (   
				  IIF(Len(LTrim(Rtrim(c.FinalLatestAnnualReport))) = 4, SUBSTRING(c.FinalLatestAnnualReport, 3, 2)  , 'XX' ) 
				  + '/' 
				  + IIF(Len(ltrim(rtrim(c.FinalLatestSustainabilityReport))) = 4, SUBSTRING(c.FinalLatestSustainabilityReport, 3, 2)  , 'XX' ) 
				  + '/'
				  + IIF(c.FinalLatestRequestedFeedback is null, 'XXXX'  ,  Format(c.FinalLatestRequestedFeedback, 'MMyy' ) ) 
				  + '/'
				  + IIF(c.FinalLatestReceivedFeedback is null, 'XXXX'  ,  Format(c.FinalLatestReceivedFeedback, 'MMyy' )  ) 
				  + '/'
				  + 'XXXX' -- lastFinalControversyUpdate is assumed null
				
				 ) as SPLCode                     
                , c.LastComprehensiveUpdate as LastUpdate 


		from dsrsystem.dbo.companies c		 
		inner join RootPeergroups p on c.PeerGroupSector = p.SectorId
		inner join dsrsystem.[dbo].CompanyType ct on c.Type = ct.Type
		inner join GICSStructure g on c.GICSSector = g.SectorId
		left outer join CompaniesWithIndicators i on c.companyId = i.CompanyId
		WHERE N'Dummy Company' <>  c.Name AND LastCorporateDataUpdate >= '1900-01-01'
	)
	 
	 Insert into dbo.CorporateDataFromDSR select * from CorporateDatas order by id; 



END



GO


